﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace HustQQServer.DataBase
{
    public class UserDB
    {
        public static string GetIP(string QQ_ID)
        {
            string commandString = "SELECT IP FROM User_Info WHERE QQ_ID = " + QQ_ID;
            try
            {
                DbManager.Connect();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString,DbManager.connectString);
                DbManager.Disconnect();
                DataSet dataSet = new DataSet();

           
                dataAdapter.Fill(dataSet, "IPs");
                if (dataSet.Tables["IPs"].Rows.Count > 0)
                    return (string)dataSet.Tables["IPs"].Rows[0][0];
                else
                    return null;
            }
            catch
            {
                return null;
            }
        }

        public static string GetPassword(string QQ_ID)
        {
            string commandString = "SELECT Password FROM User_Info WHERE QQ_ID = " + QQ_ID;
            try
            {
                DbManager.Connect();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString,DbManager.connectString);
                DbManager.Disconnect();
                DataSet dataSet = new DataSet();
            
                dataAdapter.Fill(dataSet, "Password");
                if (dataSet.Tables["Password"].Rows.Count > 0)
                    return (string)dataSet.Tables["Password"].Rows[0][0];
                else
                    return null;

            }
            catch (SqlException e)
            {
                return null;
            }
        }

        public static List<HustQQModel.User> GetFriends(string QQ_ID)
        {
            List<HustQQModel.User> myFriends = new List<HustQQModel.User>();
            try
            {
                string commandString = "SELECT FriendID, FriendClass FROM Friend WHERE QQ_ID = " + QQ_ID;
                DbManager.Connect();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString,DbManager.connectString);
                DbManager.Disconnect();
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet, "friends");
                
                for (int i = 0; i < dataSet.Tables["friends"].Rows.Count; i++)
                {
                    HustQQModel.User usr = new HustQQModel.User();
                    
                    usr.qq_ID = (string)dataSet.Tables["friends"].Rows[i][0];
                    usr.group = (string)dataSet.Tables["friends"].Rows[i][1];
                    myFriends.Add(usr);
                }

                return myFriends;
            }
            catch
            {
                return null;
            }
        }

        public static bool addAFriend(string qq_ID, string friendId)
        {
            int item;
            string commandString = "INSERT INTO Friend " +
                "VALUES(" + qq_ID +", " + friendId + ", '我的好友')";
            try
            {

                if (!DbManager.Connect())
                {
                    return false;
                }
                SqlCommand command = new SqlCommand();
                command.Connection = DbManager.MyConnection;
                item = command.ExecuteNonQuery();
                command.CommandText = commandString;
                
                DbManager.Disconnect();
            }
            catch (SqlException e)
            {
                return false;
            }

            if (item == 1)
                return true;
            else
                return false;
        }

        public static bool addAFriend(string qq_ID, string friendId, string group)
        {
            int item;
            string commandString = "INSERT INTO Friend " +
                "VALUES(" + qq_ID + ", " + friendId + ", " + group + ")";
            try
            {

                if (!DbManager.Connect())
                {
                    return false;
                }
                SqlCommand command = new SqlCommand();
                command.Connection = DbManager.MyConnection;
                command.CommandText = commandString;
                item = command.ExecuteNonQuery();
                DbManager.Disconnect();
            }
            catch (SqlException e)
            {
                return false;
            }

            if (item == 1)
                return true;
            else
                return false;
        }




        public static void deleteAFriend(string qq_ID, string friendId)
        {
            string commandString = "DELETE FROM Friend " +
                "WHERE QQ_ID = " + qq_ID + " AND FriendID = " + friendId;
            try
            {

                if (!DbManager.Connect())
                {
                    return;
                }
                SqlCommand command = new SqlCommand();
                command.Connection = DbManager.MyConnection;
                command.CommandText = commandString;
                command.ExecuteNonQuery();
                DbManager.Disconnect();
            }
            catch (SqlException e)
            {
                return;
            }
        }

        public static string InsertUsr(HustQQModel.User u)
        {
            string id = null;
            Random ran = new Random(DateTime.Now.Millisecond);
            while (id == null)
            {
                id = ran.Next(999999).ToString();
                string commandString = "INSERT INTO User_Info (QQ_ID, IP, Password, Name, Age, Sex, Birthday, Other, Telephone, Address, Note, Port ,Face) " +
                "VALUES('" + id + "','" + u.ip + "','"+ u.password + "','" + u.name.ToString() + "','" + u.age.ToString() + "','" + u.sex.ToString() +
                "','" + u.birthday + "','" + u.other + "','" + u.telephone + "','" + u.address + "','" + u.note + "','" + u.port + "','" + u.face.ToString() + "')";
                try
                {

                    if (!DbManager.Connect())
                    {
                        return null;
                    }
                    SqlCommand command = new SqlCommand();
                    command.Connection = DbManager.MyConnection;
                    command.CommandText = commandString;
                    if (command.ExecuteNonQuery() != 1)
                    {
                        id = null;
                    }
                    DbManager.Disconnect();
                }
                catch (SqlException e)
                {
                    id = null;
                    //return null;
                }
            }
            
            return id;
        }

        public static void UpdateUsr(HustQQModel.User u)//没有用
        {
            string commandString = "UPDATE User_Info " +
                "SET IP = " + u.ip + ", Password = " + u.password + ", Name = " + u.name + ", Age = " + u.age + ", Sex = " + u.sex +
                ", Birthday = " + u.birthday + ", Other = " + u.other + ", Telephone = " + u.telephone + ", Address = " + u.address + ", Note = " + u.note + ", Port = " + u.port +", Face = " + u.face + ")";
            try
            {

                if (!DbManager.Connect())
                {
                    return;
                }
                SqlCommand command = new SqlCommand();
                command.Connection = DbManager.MyConnection;
                command.CommandText = commandString;
                command.ExecuteNonQuery();
                DbManager.Disconnect();
            }
            catch (SqlException e)
            {
                return;
            }
        }

        public static HustQQModel.User GetUserInfo(string userId,bool isDetail)
        {
            HustQQModel.User usr = new HustQQModel.User();
            string commandString = "SELECT * FROM User_info WHERE  QQ_ID = " + "'" + userId + "'";

            try
            {
                DbManager.Connect();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString,DbManager.connectString);
                DbManager.Disconnect();
                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet, "Info");
                for (int i = 0; i < dataSet.Tables["Info"].Rows.Count; i++)
                {
                    usr.qq_ID = userId;

                    usr.name = (string)dataSet.Tables["Info"].Rows[i][3];
                    usr.age = (int)dataSet.Tables["Info"].Rows[i][4];
                    usr.sex = (int)dataSet.Tables["Info"].Rows[i][5];
                    usr.birthday = (string)dataSet.Tables["Info"].Rows[i][6];
                    usr.other = (string)dataSet.Tables["Info"].Rows[i][7];
                    usr.telephone = (string)dataSet.Tables["Info"].Rows[i][8];
                    usr.address = (string)dataSet.Tables["Info"].Rows[i][9];
                    usr.note = (string)dataSet.Tables["Info"].Rows[i][10];
                    usr.face = (int)dataSet.Tables["Info"].Rows[i][12];
                    
                    if (isDetail)
                    {
                        usr.ip = (string)dataSet.Tables["Info"].Rows[i][1];
                        usr.password = (string)dataSet.Tables["Info"].Rows[i][2];
                        usr.port = (string)dataSet.Tables["Info"].Rows[i][11];
                    }
                    else{
                        usr.ip = usr.password = usr.port = "";
                    }
                }

                return usr;
                
            }
            catch (Exception e)
            {
                return null;
            }
            
        }
    }


}
